﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.OracleClient;
using WF_Business;
using WF_DataAccess;

namespace ExtBusiness
{
    /// <summary>
    /// xt_submit_info操作类
    /// </summary>
    public class ClsSubmitInfo
    {
        /// <summary>
        /// 获取全部内容
        /// </summary>
        /// <returns></returns>
        public static DataTable GetSubmit()
        {
            DataTable dtTemp;
            string strSql = "select * from SYS_SUBMIT";

            SysParams.OAConnection().RunSql(strSql, out dtTemp);
            return dtTemp;
        }

        /// <summary>
        /// 修改是否允许上传标志
        /// </summary>
        /// <param name="strID"></param>
        /// <param name="nIsFlag"></param>
        public static void UpdateIsFlag(string strID, int nIsFlag)
        {
            string strSql = string.Format(@"update SYS_SUBMIT set is_submit={1} where ID='{0}'", strID, nIsFlag);

            SysParams.OAConnection().RunSql(strSql);
        }

        /// <summary>
        /// 获取是否已提交标志
        /// </summary>
        /// <param name="strId"></param>
        /// <returns></returns>
        public static string GetIsSubmit(string strId)
        {
            string strIsSubmit = "";
            if (!string.IsNullOrEmpty(strId))
            {
                string strSql = string.Format("SELECT is_submit FROM SYS_SUBMIT WHERE ID = '{0}'", strId);

                strIsSubmit = SysParams.OAConnection().GetValue(strSql);
            }
            return strIsSubmit;
        }

        /// <summary>
        /// 获取已提交事项
        /// </summary>
        /// <param name="strName">The STR name.</param>
        /// <returns></returns>
        public DataTable GetAlreadySubmit(string strName)
        {
            DataTable dtTemp;
            string strSql = string.Format(@"select * from xt_submit_info 
                where submitflag=1 and flowname='{0}' order by iid asc",strName);

            SysParams.OAConnection().RunSql(strSql, out dtTemp);
            return dtTemp;
        }

        /// <summary>
        /// 修改上传标志
        /// </summary>
        /// <param name="strIID">办件编号</param>
        /// <param name="nFlag">提交状态位</param>
        public static void UpdateFlag(string strIID, int nFlag)
        {
            string strSql = string.Format(@"update xt_submit_info set submitflag={1} where IID='{0}'", strIID, nFlag);

            SysParams.OAConnection().RunSql(strSql);
        }


        /// <summary>
        /// 根据wid获取flowname
        /// </summary>
        /// <param name="strWid">The STR wid.</param>
        /// <returns></returns>
        public string GetWnameWithWid(string strWid)
        {
            string strReturn = "";
            if (!string.IsNullOrEmpty(strWid))
            {
                string strSql = string.Format("select wname from st_workflow a where wid='{0}'", strWid);

                strReturn = SysParams.OAConnection().GetValue(strSql);
            }
            return strReturn;
        }

        /// <summary>
        /// 根据关联id获取原有iid
        /// </summary>
        /// <param name="strIId"></param>
        /// <returns></returns>
        public string GetGlIid(string strIId)
        {
            string strSql = string.Format(@"select 关联ID from xt_instance_ext t where IID='{0}'", strIId);

            return SysParams.OAConnection().GetValue(strSql);
        }

        /// <summary>
        /// 按人员权限获取在线申报待受理信息
        /// </summary>
        /// <param name="strUserID">用户ID</param>
        /// <param name="strFlowName">流程名称</param>
        /// <param name="strIID">单位名称</param>
        /// <param name="strRequester">机构名称</param>
        /// <param name="strBusinessFlag">补正后再次申请案件的标识(0:排除,1仅限于,为空者全部包括)</param>
        /// <returns></returns>
        public DataTable GetBusinessInfo(string strUserID, string strFlowName, string strIID, string strRequester, string strBusinessFlag)
        {
            DataTable dtTemp;
            string strSql = string.Format(@"select a.iid,a.flowname,a.flowid,
       a.flowtype,
       a.requester,
       a.edittime,
       a.sync_type,
       a.SUBMITFLAG,
       b.wid,
       (select 图名 from ut_国家测绘局地图审核申请表 m where m.iid = a.iid) tm,
       case
         when c.unitsid is not null then
          (select unitsname
             from sys_units
            where id = c.unitsid
              and rownum < 2)
         else
          c.username
       end username,
       C.USERID,
       (select count(iid) from xt_instance_ext where 关联ID = a.iid) submitcount
  from xt_submit_info a, st_workflow b, sys_user c
 where a.flowname = b.wname
   and a.requester = c.userid
   and b.rot = 0
   and (a.submitflag = 1 or a.submitflag = 3)
   and a.isdelete = 0
   and not exists
 (select 关联id
          from xt_instance_ext t
         where t.关联id = a.iid
           and t.status <> '-1')
   and a.flowname = wname
   and wid in
       (select A.Wid
          from st_group_in_step A, st_user_group B
         where A.GID = B.GID
           and B.userid = '{0}')", strUserID);

            if (!string.IsNullOrEmpty(strFlowName))
            {
                strSql += string.Format(" and a.flowname='{0}'", strFlowName);
            }
            if (!string.IsNullOrEmpty(strIID))
            {
                strSql += string.Format(" and a.iid like '%{0}%'", strIID);
            }
            if (!string.IsNullOrEmpty(strRequester))
            {
                strSql += string.Format(@" and (case
                                             when c.usertype = '1' then
                                              (select unitsname
                                                 from sys_units
                                                where id = c.unitsid
                                                  and rownum < 2)
                                             else
                                              c.username
                                           end) like '%{0}%'", strRequester);
            }
            if (strBusinessFlag == "0")//排除补正后再次提交的案件
            {
                strSql += string.Format("and (select count(iid) from xt_instance_ext where 关联ID = a.iid) =0");
            }
            else if (strBusinessFlag == "1")//仅限于补正后再次提交的案件
            {
                strSql += string.Format("and (select count(iid) from xt_instance_ext where 关联ID = a.iid) >0");
            }

            strSql += " order by a.submitflag,a.iid asc";
            SysParams.OAConnection().RunSql(strSql, out dtTemp);
            return dtTemp;
        }

        /// <summary>
        /// 获取用户有权限的审批流程
        /// </summary>
        /// <param name="strUserID">用户ID</param>
        /// <returns></returns>
        /// <!--addby zhongjian 20100308-->
        public DataTable GetSerialPowerByUserID(string strUserID)
        {
            string strSql = string.Format(@"select wid,wname
                                              from st_workflow
                                             where wid in
                                                   (select distinct WID
                                                      from st_group_in_step
                                                     where rot = 0
                                                       and GID in
                                                           (select GID
                                                              from st_user_group t
                                                             where t.userid = '{0}'))", strUserID);

            DataTable dtReturn;
            SysParams.OAConnection().RunSql(strSql, out dtReturn);

            return dtReturn;
        }

        /// <summary>
        /// 设置提交状态位(以下方法理论上都应该在ExtBusiness.submit类中)
        /// </summary>
        /// <param name="strIID">办件编号</param>
        /// <param name="strField">标志位字段名称</param>
        public void SetSubmitFlag(string strIID, string strField)
        {
            string strSql = string.Format(@"update xt_submit_info set {0}='1' where iid='{1}'", strField, strIID);
            SysParams.OAConnection().RunSql(strSql);
        }

        /// <summary>
        /// 设置流程状态
        /// </summary>
        /// <param name="striid">iid</param>
        /// <param name="strStatus">状态值(0:未提交;1:已提交;2:已办结;-1:返回补证;-2:驳回中止;-3:不予受理)</param>
        /// <param name="strRemark">审批意见</param>
        /// <!--addby zhongjian 20091021-->
        public void SetSubmitStatus(string striid, string strStatus, string strRemark)
        {
            string strSql = string.Format(@"update xt_submit_info set submitflag='{1}',remark='{2}',
                edittime=sysdate where iid='{0}'", striid, strStatus, strRemark);
            SysParams.OAConnection().RunSql(strSql);
        }

        /// <summary>
        /// 获取业务补正详情列表
        /// </summary>
        /// <param name="strIID">申请编号</param>
        /// <param name="SerialIID">受理编号</param>
        /// <param name="strPri">优先级</param>
        /// <returns></returns>
        /// <!--addby zhongjian 20100628-->
        public DataTable GetCorrectionInfo(string strIID, string SerialIID, string strPri)
        {
            string strSql = string.Format(@"select a.iid,
                                               b.accepted_time,
                                               b.name,
                                               b.priority,
                                               c.wname,
                                               a.关联id,
                                               a.stop_remark,
                                               a.submittime
                                          from xt_instance_ext a, st_instance b, st_workflow c
                                         where a.iid = b.iid
                                           and b.wid = c.wid
                                           and a.status = '-1'
                                           and a.关联id = '{0}'
                                        ", strIID);
            if (!string.IsNullOrEmpty(SerialIID))
                strSql += string.Format(" and a.iid='{0}'", SerialIID);
            if (!string.IsNullOrEmpty(strPri))
                strSql += string.Format(" and b.priority='{0}'", strPri);
            strSql += " order by a.iid";

            DataTable dtReturn;
            SysParams.OAConnection().RunSql(strSql, out dtReturn);
            return dtReturn;
        }

        /// <summary>
        /// 获取业务补正原因信息
        /// </summary>
        /// <param name="SerialIID">受理编号</param>
        /// <returns></returns>
        /// <!--addby zhongjian 20100628-->
        public string GetFieldFromInsExt(string SerialIID,string strFiledName)
        {
            string strSql = string.Format(@"select {1} from xt_instance_ext a where a.iid = '{0}'", SerialIID,strFiledName);
            return SysParams.OAConnection().GetValue(strSql);
        }

        /// <summary>
        /// 获取流程状态
        /// </summary>
        /// <param name="strIID">流程IID(如:1000000039386)</param>
        /// <returns></returns>
        /// <!--addby zhongjian 20100323-->
        public DataTable GetSerialSubmitflag(string strIID)
        {
            string strSql = string.Format(@"select submitflag from xt_submit_info where iid = '{0}'", strIID);
            DataTable dtReturn;
            SysParams.OAConnection().RunSql(strSql, out dtReturn);
            return dtReturn;
        }

        /// <summary>
        /// 获取所有待办事项列表，以方便变更申请
        /// </summary>
        /// <returns></returns>
        public DataTable GetDaiBanInfoForBg()
        {
            string strSql = @"select * from xt_submit_info a
 where (is_bg = '2' or is_yq = '2' or is_cx = '2')
   and a.submitflag = 3
   and a.iid in (select b.关联id from xt_instance_ext b,st_instance c where b.关联id is not null and b.iid=c.iid and c.status=1) order by inputdate desc";
            DataTable dtReturn;
            SysParams.OAConnection().RunSql(strSql, out dtReturn);
            return dtReturn;
        }

        /// <summary>
        /// 发送用户消息,并更新用户消息步骤
        /// </summary>
        /// <param name="striid">案件IID</param>
        /// <param name="strStepName">消息步骤</param>
        public void SetMsg(string striid, string strStepName)
        {
            string strSql = string.Empty;
            DataTable dtTemp = new DataTable();
            IDataAccess idaTemp = SysParams.OAConnection(true);

            try
            {
                //获取用户信息
                strSql = string.Format("select username,userid,mobile from sys_user where userid = (select requester from xt_submit_info where iid ='{0}')", striid);
                idaTemp.RunSql(strSql, out dtTemp);

                if (dtTemp.Rows.Count > 0)
                {
                    string strUserID = dtTemp.Rows[0]["userid"].ToString();
                    string strUserName = dtTemp.Rows[0]["username"].ToString();
                    string strMobile = dtTemp.Rows[0]["mobile"].ToString();
                    DataTable dtMsg = new DataTable();
                    //获取消息内容
                    strSql = string.Format("select step_no,step_msg from xt_request_step where step_name='{0}'", strStepName);
                    idaTemp.RunSql(strSql, out dtMsg);

                    if (dtMsg.Rows.Count > 0)
                    {
                        string strStepNo = dtMsg.Rows[0]["step_no"].ToString();
                        string strStepMsg = dtMsg.Rows[0]["step_msg"].ToString();
                        if (!string.IsNullOrEmpty(strStepMsg))
                        {
                            //组合消息内容 addby zhongjian 20091207
                            strSql = string.Format("select flowtype from xt_submit_info where iid='{0}'", striid);
                            string strFlowType = idaTemp.GetValue(strSql);
                            strStepMsg = string.Format(strStepMsg, strUserName, strFlowType);

                            //发送信息 
                            strSql = string.Format(@"Insert into xt_messagebox(MESSAGETEXT,PHONENO,USERNAME,USERID) values ('{0}','{1}','{2}','{3}')", strStepMsg, strMobile, strUserName, strUserID);
                            idaTemp.RunSql(strSql);
                        }

                        //更新用户通知步骤值 
                        strSql = string.Format(@"update sys_user set stepno='{0}' where userid='{1}'", strStepNo, strUserID);
                        idaTemp.RunSql(strSql);
                    }
                }

                idaTemp.Close(true);
            }
            catch
            {
                idaTemp.Close(false);
                throw;
            }
        }

        /// <summary>
        /// 根据流程名称获取一个流程中的所有表格
        /// </summary>
        /// <param name="strFlowName">流程名称</param>
        /// <returns></returns>
        /// <!--addby zhongjian 20091229-->
        public DataTable GetTableanName(string strFlowName)
        {
            string strSql = string.Format(@"select a.TABLEANOTHERNAME, a.FID, a.tablename, a.flowname
                                              from XT_PROCESS a, xt_workflow_define b
                                             where a.flowid = b.id
                                               and b.ispub = '1'
                                               and b.isdelete = '0'
                                               and b.flowname = '{0}'
                                             ORDER BY a.orderdield", strFlowName);

            DataTable dtReturn;
            SysParams.OAConnection().RunSql(strSql, out dtReturn);

            return dtReturn;
        }

        /// <summary>
        /// 更新案件的提交状态为“预审”
        /// </summary>
        /// <param name="striid">办件编号</param>
        /// <param name="nSubmitFlag">提交标志位（0:未提交;1:已提交;2:已通过;3:预审;4:协审通过;-1:返回补证;-2:驳回中止;-3:不予受理;-4:审批不通过）</param>
        public void SetYsFlag(string striid,int nSubmitFlag)
        {
            string strSql = string.Format(@"update xt_submit_info set submitflag='{1}'
                 where iid='{0}'", striid,nSubmitFlag);
            SysParams.OAConnection().RunSql(strSql);
        }

    }
}
